<!DOCTYPE HTML>
<html lang="zh-CN">


<head>
    <meta charset="utf-8">
    <meta name="keywords" content="MySQL相关基础与SQL索引优化分析, Java,MySQL,MATLAB">
    <meta name="description" content="


一、MySQL基本知识1. MySQL简介1.1 什么是MySQL​    MySQL是一个开源的关系型数据库，由MySQL AB公司开发，目前已被Oracle收购。其遵循GPL协议，用户可根据需求定制化开发资金的MySQL。MySQ">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no">
    <meta name="renderer" content="webkit|ie-stand|ie-comp">
    <meta name="mobile-web-app-capable" content="yes">
    <meta name="format-detection" content="telephone=no">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <title>MySQL相关基础与SQL索引优化分析 | 补 阙 阁</title>
    <link rel="icon" type="image/png" href="/favicon.png">

    <link rel="stylesheet" type="text/css" href="/libs/awesome/css/all.css">
    <link rel="stylesheet" type="text/css" href="/libs/materialize/materialize.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/aos/aos.css">
    <link rel="stylesheet" type="text/css" href="/libs/animate/animate.min.css">
    <link rel="stylesheet" type="text/css" href="/libs/lightGallery/css/lightgallery.min.css">
    <link rel="stylesheet" type="text/css" href="/css/matery.css">
    <link rel="stylesheet" type="text/css" href="/css/my.css">
    
    <script src="/libs/jquery/jquery.min.js"></script>
    
<meta name="generator" content="Hexo 4.2.0"><link rel="alternate" href="/atom.xml" title="补 阙 阁" type="application/atom+xml">
<link rel="stylesheet" href="/css/prism-tomorrow.css" type="text/css"></head>


<body>
    <header class="navbar-fixed">
    <nav id="headNav" class="bg-color nav-transparent">
        <div id="navContainer" class="nav-wrapper head-container">
            <div class="brand-logo">
                <a href="/" class="waves-effect waves-light">
                    
                    <img src="/medias/logo.png" class="logo-img" alt="LOGO">
                    
                    <span class="logo-span">补 阙 阁</span>
                </a>
            </div>
            

<a href="#" data-target="mobile-nav" class="sidenav-trigger button-collapse"><i class="fas fa-bars"></i></a>
<ul class="right nav-menu">
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/" class="waves-effect waves-light">
      
      <i class="fas fa-home" style="zoom: 0.6;"></i>
      
      <span>首页</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/tags" class="waves-effect waves-light">
      
      <i class="fas fa-tags" style="zoom: 0.6;"></i>
      
      <span>标签</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/categories" class="waves-effect waves-light">
      
      <i class="fas fa-bookmark" style="zoom: 0.6;"></i>
      
      <span>分类</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/archives" class="waves-effect waves-light">
      
      <i class="fas fa-archive" style="zoom: 0.6;"></i>
      
      <span>归档</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/about" class="waves-effect waves-light">
      
      <i class="fas fa-user-circle" style="zoom: 0.6;"></i>
      
      <span>关于</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/contact" class="waves-effect waves-light">
      
      <i class="fas fa-comments" style="zoom: 0.6;"></i>
      
      <span>留言板</span>
    </a>
    
  </li>
  
  <li class="hide-on-med-and-down nav-item">
    
    <a href="/friends" class="waves-effect waves-light">
      
      <i class="fas fa-address-book" style="zoom: 0.6;"></i>
      
      <span>友情链接</span>
    </a>
    
  </li>
  
  <li>
    <a href="#searchModal" class="modal-trigger waves-effect waves-light">
      <i id="searchIcon" class="fas fa-search" title="搜索" style="zoom: 0.85;"></i>
    </a>
  </li>
</ul>

<div id="mobile-nav" class="side-nav sidenav">

    <div class="mobile-head bg-color">
        
        <img src="/medias/logo.png" class="logo-img circle responsive-img">
        
        <div class="logo-name">补 阙 阁</div>
        <div class="logo-desc">
            
            言念君子，当温其如玉
            
        </div>
    </div>

    

    <ul class="menu-list mobile-menu-list">
        
        <li class="m-nav-item">
	  
		<a href="/" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-home"></i>
			
			首页
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/tags" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-tags"></i>
			
			标签
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/categories" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-bookmark"></i>
			
			分类
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/archives" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-archive"></i>
			
			归档
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/about" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-user-circle"></i>
			
			关于
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/contact" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-comments"></i>
			
			Contact
		</a>
          
        </li>
        
        <li class="m-nav-item">
	  
		<a href="/friends" class="waves-effect waves-light">
			
			    <i class="fa-fw fas fa-address-book"></i>
			
			友情链接
		</a>
          
        </li>
        
        
        <li><div class="divider"></div></li>
        <li>
            <a href="https://github.com/copyers" class="waves-effect waves-light" target="_blank">
                <i class="fab fa-github-square fa-fw"></i>Fork Me
            </a>
        </li>
        
    </ul>
</div>

        </div>

        
            <style>
    .nav-transparent .github-corner {
        display: none !important;
    }

    .github-corner {
        position: absolute;
        z-index: 10;
        top: 0;
        right: 0;
        border: 0;
        transform: scale(1.1);
    }

    .github-corner svg {
        color: #0f9d58;
        fill: #fff;
        height: 64px;
        width: 64px;
    }

    .github-corner:hover .octo-arm {
        animation: a 0.56s ease-in-out;
    }

    .github-corner .octo-arm {
        animation: none;
    }

    @keyframes a {
        0%,
        to {
            transform: rotate(0);
        }
        20%,
        60% {
            transform: rotate(-25deg);
        }
        40%,
        80% {
            transform: rotate(10deg);
        }
    }
</style>

<a href="https://github.com/copyers" class="github-corner tooltipped hide-on-med-and-down" target="_blank"
   data-tooltip="Fork Me" data-position="left" data-delay="50">
    <svg viewBox="0 0 250 250" aria-hidden="true">
        <path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path>
        <path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2"
              fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path>
        <path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z"
              fill="currentColor" class="octo-body"></path>
    </svg>
</a>
        
    </nav>

</header>

    



<div class="bg-cover pd-header post-cover" style="background-image: url('/medias/featureimages/14.jpg')">
    <div class="container" style="right: 0px;left: 0px;">
        <div class="row">
            <div class="col s12 m12 l12">
                <div class="brand">
                    <h1 class="description center-align post-title">MySQL相关基础与SQL索引优化分析</h1>
                </div>
            </div>
        </div>
    </div>
</div>




<main class="post-container content">

    
    <link rel="stylesheet" href="/libs/tocbot/tocbot.css">
<style>
    #articleContent h1::before,
    #articleContent h2::before,
    #articleContent h3::before,
    #articleContent h4::before,
    #articleContent h5::before,
    #articleContent h6::before {
        display: block;
        content: " ";
        height: 100px;
        margin-top: -100px;
        visibility: hidden;
    }

    #articleContent :focus {
        outline: none;
    }

    .toc-fixed {
        position: fixed;
        top: 64px;
    }

    .toc-widget {
        width: 345px;
        padding-left: 20px;
    }

    .toc-widget .toc-title {
        margin: 35px 0 15px 0;
        padding-left: 17px;
        font-size: 1.5rem;
        font-weight: bold;
        line-height: 1.5rem;
    }

    .toc-widget ol {
        padding: 0;
        list-style: none;
    }

    #toc-content {
        height: calc(100vh - 250px);
        overflow: auto;
    }

    #toc-content ol {
        padding-left: 10px;
    }

    #toc-content ol li {
        padding-left: 10px;
    }

    #toc-content .toc-link:hover {
        color: #42b983;
        font-weight: 700;
        text-decoration: underline;
    }

    #toc-content .toc-link::before {
        background-color: transparent;
        max-height: 25px;
    }

    #toc-content .is-active-link {
        color: #42b983;
    }

    #toc-content .is-active-link::before {
        background-color: #42b983;
    }

    #floating-toc-btn {
        position: fixed;
        right: 15px;
        bottom: 76px;
        padding-top: 15px;
        margin-bottom: 0;
        z-index: 998;
    }

    #floating-toc-btn .btn-floating {
        width: 48px;
        height: 48px;
    }

    #floating-toc-btn .btn-floating i {
        line-height: 48px;
        font-size: 1.4rem;
    }
</style>
<div class="row">
    <div id="main-content" class="col s12 m12 l9">
        <!-- 文章内容详情 -->
<div id="artDetail">
    <div class="card">
        <div class="card-content article-info">
            <div class="row tag-cate">
                <div class="col s7">
                    
                    <div class="article-tag">
                        
                            <a href="/tags/MySQL/">
                                <span class="chip bg-color">MySQL</span>
                            </a>
                        
                            <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">
                                <span class="chip bg-color">数据库</span>
                            </a>
                        
                            <a href="/tags/%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/">
                                <span class="chip bg-color">索引优化</span>
                            </a>
                        
                            <a href="/tags/SQL/">
                                <span class="chip bg-color">SQL</span>
                            </a>
                        
                    </div>
                    
                </div>
                <div class="col s5 right-align">
                    
                    <div class="post-cate">
                        <i class="fas fa-bookmark fa-fw icon-category"></i>
                        
                            <a href="/categories/MySQL/" class="post-category">
                                MySQL
                            </a>
                        
                    </div>
                    
                </div>
            </div>

            <div class="post-info">
                
                <div class="post-date info-break-policy">
                    <i class="far fa-calendar-minus fa-fw"></i>发布日期:&nbsp;&nbsp;
                    2020-02-27
                </div>
                

                
                <div class="post-date info-break-policy">
                    <i class="far fa-calendar-check fa-fw"></i>更新日期:&nbsp;&nbsp;
                    2020-02-27
                </div>
                

                
                <div class="info-break-policy">
                    <i class="far fa-file-word fa-fw"></i>文章字数:&nbsp;&nbsp;
                    4.7k
                </div>
                

                
                <div class="info-break-policy">
                    <i class="far fa-clock fa-fw"></i>阅读时长:&nbsp;&nbsp;
                    17 分
                </div>
                
				
                
                    <div id="busuanzi_container_page_pv" class="info-break-policy">
                        <i class="far fa-eye fa-fw"></i>阅读次数:&nbsp;&nbsp;
                        <span id="busuanzi_value_page_pv"></span>
                    </div>
				
            </div>
            
        </div>
        <hr class="clearfix">
        <div class="card-content article-card-content">
            <div id="articleContent">
                <img src="https://img-blog.csdnimg.cn/20200227105100741.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1l1ZVlpbmdHdWFuZw==,size_16,color_FFFFFF,t_70" alt="" style="width:100%">
<a id="more"></a>

<h2 id="一、MySQL基本知识"><a href="#一、MySQL基本知识" class="headerlink" title="一、MySQL基本知识"></a>一、MySQL基本知识</h2><h3 id="1-MySQL简介"><a href="#1-MySQL简介" class="headerlink" title="1. MySQL简介"></a>1. MySQL简介</h3><h4 id="1-1-什么是MySQL"><a href="#1-1-什么是MySQL" class="headerlink" title="1.1 什么是MySQL"></a>1.1 什么是MySQL</h4><p>​    MySQL是一个开源的关系型数据库，由MySQL AB公司开发，目前已被Oracle收购。其遵循GPL协议，用户可根据需求定制化开发资金的MySQL。MySQL可移植性高，支持多种语言，例如：Java、PHP、C++、Python、Perl、Eiffel、Rubby等。其使用标准的SQL数据语言形式，可通过如下数据语言进行使用：</p>
<ul>
<li><p>DQL：数据查询语言，select、from、where；</p>
</li>
<li><p>DML：数据操作语言，insert、delete、update；</p>
</li>
<li><p>DDL：数据定义语言，create、alter、drop、truncate；</p>
</li>
<li><p>DCL：数据控制语言，grant、rollback、commit</p>
<p>MySQL常用的SQL说明：</p>
</li>
</ul>
<table>
<thead>
<tr>
<th align="center">SQL</th>
<th align="center">描述</th>
<th align="center">备注</th>
</tr>
</thead>
<tbody><tr>
<td align="center">show databases</td>
<td align="center">列出所有数据库</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">create database test01</td>
<td align="center">创建数据库test01</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">create database test01 character set utf8</td>
<td align="center">创建数据库，并设置字符集为utf-8</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">show create database test01</td>
<td align="center">查看数据库字符集</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">show variables like ’%char%‘</td>
<td align="center">查询参数</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">set [字符集属性]=utf8</td>
<td align="center">设置字符集属性为utf8</td>
<td align="center">仅临时更改，如需彻底修改需改配置文件</td>
</tr>
<tr>
<td align="center">alter database test01 character set ‘utf8’</td>
<td align="center">修改数据库字符集</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">alter table table01 convert to character set ’utf8‘</td>
<td align="center">修改数据表字符集</td>
<td align="center"></td>
</tr>
</tbody></table>
<p>MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大支持4GB，64位系统表文件最大支持8TB。在200万条记录下不加索引性能依旧较好。</p>
<p>注意：</p>
<pre class=" language-markdown"><code class="language-markdown">字符集需要安装之后立即修改，如果插入数据之后再进行修改则之前数据库、表格及数据依然为原编码格式！</code></pre>
<h3 id="2-安装MySQL"><a href="#2-安装MySQL" class="headerlink" title="2. 安装MySQL"></a>2. 安装MySQL</h3><h4 id="2-1-Windows安装"><a href="#2-1-Windows安装" class="headerlink" title="2.1 Windows安装"></a>2.1 Windows安装</h4><p>​    以5.7.28为例：参考博文<a href="https://blog.csdn.net/YueYingGuang/article/details/103410868" target="_blank" rel="noopener" title="MySQL 5.7.28安装最稳教程">MySQL 5.7.28安装最稳教程</a></p>
<h4 id="2-2-Linux安装"><a href="#2-2-Linux安装" class="headerlink" title="2.2 Linux安装"></a>2.2 Linux安装</h4><h5 id="2-2-1-docker安装"><a href="#2-2-1-docker安装" class="headerlink" title="2.2.1 docker安装"></a>2.2.1 docker安装</h5><p><a href="https://blog.csdn.net/YueYingGuang/article/details/103577110" target="_blank" rel="noopener">    CentOS 7 使用docker安装mysql</a></p>
<h5 id="2-2-2-yum-安装"><a href="#2-2-2-yum-安装" class="headerlink" title="2.2.2 yum 安装"></a>2.2.2 yum 安装</h5><pre class=" language-bash"><code class="language-bash">yum <span class="token function">install</span> mysql</code></pre>
<h5 id="2-2-3-rpm安装"><a href="#2-2-3-rpm安装" class="headerlink" title="2.2.3 rpm安装"></a>2.2.3 rpm安装</h5><pre class=" language-bash"><code class="language-bash">rpm -qa<span class="token operator">|</span><span class="token function">grep</span> mysql
rpm -ivh MySQL-client-xxx.linu.xxx.rpm
rpm -ivh MySQL-server-xxx.linu.xxx.rpm</code></pre>
<h4 id="2-3-Linux下常用命令"><a href="#2-3-Linux下常用命令" class="headerlink" title="2.3 Linux下常用命令"></a>2.3 Linux下常用命令</h4><p> tips：</p>
<ul>
<li>查看MySQL所属用户：cat /etc/passwd/|grep mysql</li>
<li>查看MySQL所属组：cat /etc/group/|grep mysql</li>
<li>查看MySQL当前服务状态：service mysql status</li>
<li>启动关闭MySQL： service mysql start、service mysql stop、service mysql restart</li>
<li>设置MySQL开机启动：chkconfig mysql on、chkconfig –list|grep mysql、cat /etc/inittab或者netsysv图形化操作</li>
<li>设置MySQL用户名密码：mysqladmin -u root -p 123456</li>
<li>进程查看：ps -ef|grep mysql </li>
<li>设置大小写不敏感：show variables like ’%lower_case_table_names%‘，修改my.cnf下lower_case_table_names=1，即大小写不敏感。</li>
</ul>
<h4 id="2-4-sql-mode"><a href="#2-4-sql-mode" class="headerlink" title="2.4 sql_mode"></a>2.4 sql_mode</h4><p>​    sql_mode定义了对MySQL中的语法校验规则，其默认值是空值，这种情况下是可以进行一些非法操作的，生产环境下必须将该值设置为严格模式，以下为sql_mode常用的值。</p>
<table>
<thead>
<tr>
<th align="center">参数</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">ONLY_FULL_GROUP_BY</td>
<td align="center">若select未选中group by的字段，则SQL不合法</td>
</tr>
<tr>
<td align="center">NO_AUTO_VALUE_ON_ZERO</td>
<td align="center">自增长列可插入0或者null</td>
</tr>
<tr>
<td align="center">STRICT_TRANS_TABLES</td>
<td align="center">若一个值不能插入到事务表中，则中断，对非事务表不做限制</td>
</tr>
<tr>
<td align="center">NO_ZERO_IN_DATE</td>
<td align="center">严格模式下不允许日期和月份为零</td>
</tr>
<tr>
<td align="center">NO_ZERO_DATE</td>
<td align="center">日期不允许插入零</td>
</tr>
<tr>
<td align="center">ERROR_FOR_DIVISION_BY_ZERO</td>
<td align="center">insert或update中，若数据被零除，则报错，如果未给出该模式，则返回null</td>
</tr>
<tr>
<td align="center">NO_AUTO_CREATE_USER</td>
<td align="center">禁止grant创建密码为空的用户</td>
</tr>
<tr>
<td align="center">NO_ENGINE_SUBSTITUTION</td>
<td align="center">存储引擎被禁用则报错，若未设置则使用默认的存储引擎代替</td>
</tr>
</tbody></table>
<h5 id="2-4-1-查看和修改sql-mode"><a href="#2-4-1-查看和修改sql-mode" class="headerlink" title="2.4.1 查看和修改sql_mode"></a>2.4.1 查看和修改sql_mode</h5><p>查看：select @@sql_mode</p>
<p>修改：set @@sql_mode=’’;</p>
<h4 id="2-5-用户管理"><a href="#2-5-用户管理" class="headerlink" title="2.5 用户管理"></a>2.5 用户管理</h4><table>
<thead>
<tr>
<th align="center">命令</th>
<th align="center">说明</th>
<th align="center">备注</th>
</tr>
</thead>
<tbody><tr>
<td align="center">create user z3 identified by ’123456‘</td>
<td align="center">创建名称为z3，密码为12346的用户</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">select host,user,password,select_priv,insert_priv,drop_priv from mysql.user</td>
<td align="center">查看用户和权限信息</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">set password = password(‘123456’)</td>
<td align="center">修改当前用户密码</td>
<td align="center"></td>
</tr>
<tr>
<td align="center">update mysql.user set password=password(‘123456’) where user = ‘z3’</td>
<td align="center">修改其他用户密码</td>
<td align="center">通过user表的修改需要flush privileges才能生效</td>
</tr>
<tr>
<td align="center">update mysql.user set user = ‘li4’ where user =’z3’</td>
<td align="center">修改用户名</td>
<td align="center">通过user表的修改需要flush privileges才能生效</td>
</tr>
<tr>
<td align="center">drop user li4</td>
<td align="center">删除用户</td>
<td align="center">不要通过user表删除，系统会有残留</td>
</tr>
</tbody></table>
<p>host:表示连接类型</p>
<ul>
<li>%表示所有远程通过TCP连接</li>
<li>IP地址</li>
<li>机器名</li>
<li>::1 ipv6本机地址</li>
<li>localhost</li>
</ul>
<h4 id="2-6-权限管理"><a href="#2-6-权限管理" class="headerlink" title="2.6 权限管理"></a>2.6 权限管理</h4><table>
<thead>
<tr>
<th align="center">命令</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">grant select,insert,delete,drop on mydb.* to z3@localhost</td>
<td align="center">给本地的z3用户的mydb数据库所有表授予增删改查权限</td>
</tr>
<tr>
<td align="center">grant all privileges on *.* to z3@’%’ identified by ‘123’</td>
<td align="center">授予通过网络登录的z3用户对所有库的权限，密码为123</td>
</tr>
<tr>
<td align="center">show grants</td>
<td align="center">查看权限</td>
</tr>
<tr>
<td align="center">revoke all privileges on mysql.* from z3@localhost</td>
<td align="center">收回z3全库全表所有权限</td>
</tr>
</tbody></table>
<hr>
<h2 id="二、SQL优化"><a href="#二、SQL优化" class="headerlink" title="二、SQL优化"></a>二、SQL优化</h2><p>一般使用SQL操作数据库时，当遇到SQL执行时间太长怎么办呢？一般我们都是从下面四个方面进行查摆问题一一优化：</p>
<ul>
<li>查询语句过于粗糙</li>
<li>索引失效，明明增加了单值索引或者复合索引，但是explain查看执行计划却发现依旧是全表扫描</li>
<li>系统设计问题导致join关联查询太多</li>
<li>硬件方面需要优化，各缓冲参数设置过小（比如order by所使用的sort_buffer_size）</li>
</ul>
<p>在优化开始之前，我们需要了解MySQL的逻辑架构是怎样的，也就是MySQL是如何处理我们的SQL语句的呢？</p>
<hr>
<h3 id="1-MySQL架构分析"><a href="#1-MySQL架构分析" class="headerlink" title="1. MySQL架构分析"></a>1. MySQL架构分析</h3><img src="https://img-blog.csdnimg.cn/20200227105039257.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1l1ZVlpbmdHdWFuZw==,size_16,color_FFFFFF,t_70,color_FFFFFF,t_70" alt="" style="width:100%">



<p>上图为MySQL逻辑架构图，其主要分为连接层、服务层、引擎层及存储层。</p>
<ul>
<li><p>连接层，MySQL最上层，它是一些客户端和连接服务，包含本地socket通信和基于客户端连接的类似tcp/ip通信，主要完成连接处理、授权认证及相关安全方案，该层引入了线程池的概念为安全认证客户端提供线程，<strong>例如驱动连接</strong>。</p>
</li>
<li><p>服务层：</p>
<table>
<thead>
<tr>
<th align="center">Management Services &amp; Utilities</th>
<th>系统管理和控制工具</th>
</tr>
</thead>
<tbody><tr>
<td align="center">SQL Interface</td>
<td>SQL接口：接收SQL返回查询结果</td>
</tr>
<tr>
<td align="center">Parser</td>
<td>解析器：验证和解析SQL语句</td>
</tr>
<tr>
<td align="center">Optimizer</td>
<td>查询优化器：MySQL对SQL会根据优化器结果进行优化</td>
</tr>
<tr>
<td align="center">Cache&amp;Buffer</td>
<td>查询缓存：提高查询效率</td>
</tr>
</tbody></table>
</li>
<li><p>引擎层</p>
<p>可插拔式引擎的选择，负责MySQL中数据的存储和查询，目前MySQL最常用的两种数据库存储引擎为InnoDB与MyISAM，下表为二者的区别。</p>
<table>
<thead>
<tr>
<th align="center">特性</th>
<th align="center">InnoDB</th>
<th align="center">MyISAM</th>
</tr>
</thead>
<tbody><tr>
<td align="center">是否支持事务</td>
<td align="center">支持</td>
<td align="center">不支持事务</td>
</tr>
<tr>
<td align="center">是否支持外键</td>
<td align="center">支持</td>
<td align="center">不支持</td>
</tr>
<tr>
<td align="center">索引类型</td>
<td align="center">聚簇索引</td>
<td align="center">非聚簇索引</td>
</tr>
<tr>
<td align="center">是否保存表的行数</td>
<td align="center">否</td>
<td align="center">是（通过变量保存，避免select count(*) 时进行全表扫描）</td>
</tr>
<tr>
<td align="center">全文索引</td>
<td align="center">5.7以后支持</td>
<td align="center">支持</td>
</tr>
<tr>
<td align="center">是否可压缩后查询</td>
<td align="center">否</td>
<td align="center">是</td>
</tr>
<tr>
<td align="center">锁的粒度</td>
<td align="center">行锁</td>
<td align="center">表锁</td>
</tr>
<tr>
<td align="center">主键是否必须</td>
<td align="center">是（聚簇索引特性）</td>
<td align="center">否</td>
</tr>
<tr>
<td align="center">存储文件</td>
<td align="center">frm（表结构），ibd（数据文件）</td>
<td align="center">frm（表结构），myd（数据文件），myi（索引文件）</td>
</tr>
</tbody></table>
<img src="https://img-blog.csdnimg.cn/20200227105100741.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1l1ZVlpbmdHdWFuZw==,size_16,color_FFFFFF,t_70" alt="" style="width:100%">

<blockquote>
<p>tips: </p>
<p>1、聚簇索引与非聚簇索引</p>
<p>聚簇索引：数据与索引是在同一B+Tree上的，非叶子节点存放的是索引（数据的指针），叶子节点存放的是数据，该索引类型必须有主键，数据可通过主键进行查询，之后建立的索引为辅助索引，辅助索引需要两次查询，先查询到主键，然后通过主键查询数据，所以主键最好设置为自增，否则主键过大会带来额外开销；</p>
<p>非聚簇索引：B+Tree的非叶子节点和叶子节点存放的都是数据的引用地址，数据文件与索引文件是分开保存的。</p>
<p>2、<strong>InnoDB的行锁是实现在索引上的，而不是锁在物理上，如果索引失效则行锁会退化为表锁。</strong></p>
</blockquote>
</li>
<li><p>存储层<br>数据库文件的数据存储在文件系统上的方式，并通过该存储方式与存储引擎交互。</p>
</li>
</ul>
<hr>
<h3 id="2-SQL执行分析"><a href="#2-SQL执行分析" class="headerlink" title="2. SQL执行分析"></a>2. SQL执行分析</h3><h4 id="2-1-SQL查询流程"><a href="#2-1-SQL查询流程" class="headerlink" title="2.1 SQL查询流程"></a>2.1 SQL查询流程</h4><p>通过对MySQL逻辑结构的分析，我们可以知道SQL查询的流程大致为：</p>
<ol>
<li>MySQL客户端通过协议与MySQL服务器建立连接，发送查询语句；</li>
<li>MySQL检查查询缓存，缓存如果命中则直接返回，未命中则将语句交由解析器处理；</li>
<li>解析器通过关键字对SQL语句预处理，生成解析树并验证SQL语法，若语法通过则交由优化器；</li>
<li>优化器将SQL转化成执行计划并选择最好的执行；</li>
<li>优化器将最终的查询结果返回。</li>
</ol>
<p>SQL手写为：<img src="https://img-blog.csdnimg.cn/20200227105125196.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1l1ZVlpbmdHdWFuZw==,size_16,color_FFFFFF,t_70" alt="" style="width:100%"></p>
<p>优化器优化之后的顺序：</p>
<img src="https://img-blog.csdnimg.cn/20200227105138922.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1l1ZVlpbmdHdWFuZw==,size_16,color_FFFFFF,t_70" alt="" style="width:100%">



<blockquote>
<p>tips: </p>
<ul>
<li>show engines可查看所有数据库引擎</li>
<li>show variables like ‘%storage_engine%’ 可查看当前默认数据库引擎</li>
</ul>
</blockquote>
<hr>
<h4 id="2-2-show-profile"><a href="#2-2-show-profile" class="headerlink" title="2.2 show profile"></a>2.2 show profile</h4><h5 id="2-2-1-SQL执行周期"><a href="#2-2-1-SQL执行周期" class="headerlink" title="2.2.1 SQL执行周期"></a>2.2.1 SQL执行周期</h5><p>通过show profile可以查看SQL的详细执行周期，可以作为优化SQL最强有力的工具。</p>
<ol>
<li><p>通过show variables like ‘%profiling%’查看是否开启该功能；</p>
<img src="https://img-blog.csdnimg.cn/20200227105208553.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1l1ZVlpbmdHdWFuZw==,size_16,color_FFFFFF,t_70" alt="" style="width:100%">
</li>
<li><p>set profiling = 1；开启show profile；</p>
</li>
<li><p>show profile cpu,block io for query Query_id；查询SQL详细执行信息。</p>
<img src="https://img-blog.csdnimg.cn/20200227105226954.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1l1ZVlpbmdHdWFuZw==,size_16,color_FFFFFF,t_70" alt="" style="width:100%">

</li>
</ol>
<p>show profiles可以提供SQL执行的详细信息，但是实际中我们更多使用explain对SQL进行解释优化，查看SQL是否使用索引等等，下文更多是针对explain的使用进行介绍。</p>
<hr>
<h3 id="3-SQL索引优化"><a href="#3-SQL索引优化" class="headerlink" title="3. SQL索引优化"></a>3. SQL索引优化</h3><hr>
<p>如何提高SQL执行速度？许多人第一反应就是增加索引，什么是索引呢？如何增加索引且增加的索引不会因为SQL问题导致失效呢？</p>
<p>我们知道MySQL底层使用B+树实现，索引作为数据的另一种表现形式同样也会耗费存储空间的。</p>
<p>如果单纯的为了提升某个字段的查询效率而在全表字段上都增加索引，那么无疑给存储空间带来了巨大负担。而且索引增加之后虽然给查询带来了便利，但是在增加和删除方面，为了维护索引而带来的工作量也是极为繁重的，这说明了不经研究而随意建立索引的方法是不可取的。</p>
<hr>
<h4 id="3-1-索引简介"><a href="#3-1-索引简介" class="headerlink" title="3.1 索引简介"></a>3.1 索引简介</h4><h5 id="3-1-什么是索引？"><a href="#3-1-什么是索引？" class="headerlink" title="3.1 什么是索引？"></a>3.1 什么是索引？</h5><p>​        索引是帮助MySQL高效获取数据的数据结构，所以索引可以简单理解为排好序的快速查找数据结构。</p>
<h5 id="3-2-索引的优缺点"><a href="#3-2-索引的优缺点" class="headerlink" title="3.2 索引的优缺点"></a>3.2 索引的优缺点</h5><p><strong>优点：</strong></p>
<ul>
<li>提高数据检索的效率，降低数据库的IO成本；</li>
<li>索引是一种排好序的数据结构，通过索引进行排序可以降低数据的排序成本。</li>
</ul>
<p><strong>劣势：</strong></p>
<ul>
<li>降低更新表的速度，加了索引的字段在insert、update、delete时会带来额外的开销；</li>
<li>占用多余的空间。</li>
</ul>
<hr>
<h4 id="3-2-适合创建索引的情况"><a href="#3-2-适合创建索引的情况" class="headerlink" title="3.2 适合创建索引的情况"></a>3.2 适合创建索引的情况</h4><ul>
<li>主键自动建立唯一索引</li>
<li>频繁作为查询条件的字段需要建立索引</li>
<li>查询中与其他表关联的字段，外键关系建立索引</li>
<li>组合索引性价比优于单值索引</li>
<li>查询中排序的字段</li>
<li>查询中统计或者分组的字段</li>
</ul>
<hr>
<h4 id="3-3-不适合创建索引的情况"><a href="#3-3-不适合创建索引的情况" class="headerlink" title="3.3 不适合创建索引的情况"></a>3.3 不适合创建索引的情况</h4><ul>
<li>表记录太少</li>
<li>经常增删改的表或者字段</li>
<li>where条件里用不到的字段不创建索引</li>
<li>字段中重复内容较多，过滤性不好的不适合建立索引</li>
</ul>
<hr>
<h4 id="3-4-explain性能参数分析"><a href="#3-4-explain性能参数分析" class="headerlink" title="3.4 explain性能参数分析"></a>3.4 explain性能参数分析</h4><p>在日常工作中，使用最为频繁的就是通过explain+SQL模拟优化器执行SQL语句，从而知道MySQL是如何处理SQL语句的，并根据情况分析SQL的性能瓶颈以优化SQL。</p>
<p>那么，通过explain我们可以获得什么信息呢？</p>
<ul>
<li>表的读取顺序</li>
<li>数据读取操作的操作类型</li>
<li>哪些索引可以使用</li>
<li>哪些索引被实际使用</li>
<li>表之间的引用</li>
<li>每张表有多少行被优化器查询</li>
</ul>
<h5 id="3-4-1-explain参数"><a href="#3-4-1-explain参数" class="headerlink" title="3.4.1 explain参数"></a>3.4.1 explain参数</h5><p>我们通过explain+SQL的方式可以获得如下所示的列表：</p>
<img src="https://img-blog.csdnimg.cn/20200227105249657.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1l1ZVlpbmdHdWFuZw==,size_16,color_FFFFFF,t_70" alt="" style="width:100%">

<p>可以看出主要参数即：id，select_type,table,partitions,<strong>type</strong>,<strong>possible_keys,key</strong>,key_len,ref,rows,filtered,<strong>Extra</strong>。</p>
<p>以下针对第一行参数进行详细分析：</p>
<ul>
<li><p><strong>id</strong>：select查询的序列号，包含一组数字，表示查询中select子句或操作表的顺序，主要分以下三种情况：</p>
<ul>
<li>id相同，执行顺序由上至下；</li>
<li>id不同，如果是子查询，id的序号会较大，此时优先级越高，也就越先被执行；</li>
<li>id相同不同，同时存在时先根据不同定优先级，再由上至下进行执行；</li>
</ul>
</li>
<li><p>select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询，具体参数如下：</p>
<ul>
<li>SIMPLE：简单的select查询，不包含子查询或者UNION；</li>
<li>PRIMARY：查询中若包含其他子查询，则最外层会被标记为PRIMARY；</li>
<li>SUBQUERY：select或者where中包含了子查询；</li>
<li>DERIVED：From中包含的子查询，临时表；</li>
<li>UNION：第二个select出现在UNION之后则会被标记为UNION；</li>
<li>UNION RESULT：从UNION中获取结果的select；</li>
</ul>
</li>
<li><p>table：显示改行的数据来源于哪张表</p>
</li>
<li><p>partitions：是否分区</p>
</li>
<li><p><strong>type：</strong>优化器定义的访问类型，从最好到最差依次是<strong>scerria</strong>（system&gt;const&gt;ref_eq&gt;ref&gt;range&gt;index&gt;all），具体含义如下：</p>
<ul>
<li>system：表中只有一行记录，相当于const类型的特例，平时不会出现；</li>
<li>const：通过索引一次就找到了，const用于比较primary key或者unique索引；</li>
<li>eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，<strong>简单地说是const是直接按主键或唯一键读取，eq_ref用于联表查询的情况，按联表的主键或唯一键联合查询</strong>；</li>
<li>ref：非唯一性索引，返回匹配某个值的所有行（一值找多行）;</li>
<li>range：索引给定范围的行，例如where中的between、in，这种范围索引较全表扫描要好，因为范围决定了不用全表扫描；</li>
<li>index：full index，全表索引扫描，效果与all的区别就是index扫描的是全表的索引，而all扫描的是数据；</li>
<li>all：全表数据扫描，匹配到之后返回结果。</li>
</ul>
<blockquote>
<p>一般来说，保证查询达到range或者ref效果为佳。</p>
</blockquote>
</li>
<li><p><strong>possible_keys：</strong>显示可能应用在这张表中的索引，一个或多个。查询所涉及字段若存在索引，则索引将被列出，但不一定被使用</p>
</li>
<li><p><strong>key：</strong>查询中实际使用的索引，如果没有则为null，查询中如果使用了覆盖索引，则索引和查询的字段一一吻合</p>
</li>
<li><p>key_len：表示索引使用到的字节数，可通过该值计算查询中使用的索引长度。在不损失精度的情况下，该值越小越好。该值为索引最大可能长度，并非实际使用长度。</p>
</li>
<li><p>ref：显示索引哪一列被使用了，哪些列或者常数被用于索引进行查询（最好是常数）。也就是说key 列是实际使用的 index ， 但 index 可能建立在数据表的若干列上。ref 列列出具体哪些列或常数被使用了。</p>
</li>
<li><p>rows：根据表统计信息及索引使用情况大致估算除所需要读取的行数</p>
</li>
<li><p>filtered：返回结果的行占需要读到的行(rows列的值)的百分比，因为对于join操作，前一个表的结果集大小直接影响了循环的次数</p>
</li>
<li><p><strong>Extra：包含不适合在其他列中显示但十分重要的额外信息</strong>，其主要会有以下几个常数：</p>
<table>
<thead>
<tr>
<th align="center">参数</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">Using filesort</td>
<td align="center">MySQL会对数据使用外部的文件索引排序，而不是按照表内索引顺序读取，文件的建立删除会耗费大量资源，出现该项一定要优化</td>
</tr>
<tr>
<td align="center">Using temporary</td>
<td align="center">使用临时表保存中间结果，常见于order by、group by，临时表的创建与删除会耗费大量资源，出现该项一定要优化</td>
</tr>
<tr>
<td align="center">Using index</td>
<td align="center">相应的select中使用了覆盖索引，避免访问冗余数据，效果不错，同时出现Using where则表明索引被用来执行索引键的查找，若没有出现Using where 则表明索引用于读取数据而非执行查找</td>
</tr>
<tr>
<td align="center">Using where</td>
<td align="center">表明使用了where过滤</td>
</tr>
<tr>
<td align="center">Using join buffer</td>
<td align="center">使用了连接缓存，可在my.cnf中增加缓存大小</td>
</tr>
<tr>
<td align="center">impossible where</td>
<td align="center">where的值总是false，不能获取任何值</td>
</tr>
<tr>
<td align="center">select tables optimized away</td>
<td align="center">在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。</td>
</tr>
<tr>
<td align="center">distinct</td>
<td align="center">优化distinct，找到第一匹配值即停止</td>
</tr>
</tbody></table>
</li>
</ul>
<p>以上就是对explain参数的分析，通过对这些参数的学习，将使得我们以后在SQL优化方面无论遇到什么问题都能对症下药，迎刃而解。</p>
<hr>
<h4 id="3-5-索引失效情况分析"><a href="#3-5-索引失效情况分析" class="headerlink" title="3.5 索引失效情况分析"></a>3.5 索引失效情况分析</h4><p>有时候我们明明在字段上建立了索引，但是explain之后发现优化器并没有使用到索引，那什么情况下会导致索引失效？写SQL语句时应该注意些什么才能避免索引失效呢？</p>
<h5 id="3-5-1-索引失效"><a href="#3-5-1-索引失效" class="headerlink" title="3.5.1 索引失效"></a>3.5.1 索引失效</h5><p>以下是常见导致索引失效的几种情况：</p>
<ol>
<li>最左前缀法则：建立的复合索引在使用时没有遵循最左前缀法则，例如索引使用了（a,b,c）三个字段，但是使用的时候a没有使用到，索引失效；</li>
<li>在索引字段上进行计算，函数处理，类型转换等二次操作；</li>
<li>范围条件右边的列索引失效，即范围之后全失效，例如索引为（id,age,name）,使用时如果出现where id=xx and age&gt;10 and name=xx，则age之后索引失效；</li>
<li>尽量使用覆盖索引，避免select *，即复合索引的字段与查询字段一一吻合；</li>
<li>!=或&lt;&gt;会导致索引失效；</li>
<li>is null，is not null会导致索引失效；</li>
<li>使用like模糊查询时如果%出现在最左边则索引失效，解决办法就是使用覆盖索引；</li>
<li>字符串不加单引号；</li>
<li>多次使用or会导致索引失效。</li>
</ol>
<p>以上就是会造成MySQL索引失效的一些情况，为了避免这些情况的出现而造成索引失效，建议：</p>
<ul>
<li>对于单值索引，尽量选择针对查询过滤性更好的字段来建立索引，例如性别只有男、女、未知的三类就没有必要建立索引；</li>
<li>复合索引过滤性最好的字段要放在最前面；</li>
<li>复合索引尽可能包含查询字段中where子句中更多的字段；</li>
<li>通过分析统计信息和调整SQL查询的写法来选择建立合适的索引。</li>
</ul>
<script>
        document.querySelectorAll('.github-emoji')
          .forEach(el => {
            if (!el.dataset.src) { return; }
            const img = document.createElement('img');
            img.style = 'display:none !important;';
            img.src = el.dataset.src;
            img.addEventListener('error', () => {
              img.remove();
              el.style.color = 'inherit';
              el.style.backgroundImage = 'none';
              el.style.background = 'none';
            });
            img.addEventListener('load', () => {
              img.remove();
            });
            document.body.appendChild(img);
          });
      </script>
            </div>
            <hr/>

            

    <div class="reprint" id="reprint-statement">
        
            <div class="reprint__author">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-user">
                        文章作者:
                    </i>
                </span>
                <span class="reprint-info">
                    <a href="https://exclusiver.gitee.io" rel="external nofollow noreferrer">Doug Li</a>
                </span>
            </div>
            <div class="reprint__type">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-link">
                        文章链接:
                    </i>
                </span>
                <span class="reprint-info">
                    <a href="https://exclusiver.gitee.io/2020/02/27/mysql-xiang-guan-ji-chu-yu-sql-suo-yin-you-hua-fen-xi/">https://exclusiver.gitee.io/2020/02/27/mysql-xiang-guan-ji-chu-yu-sql-suo-yin-you-hua-fen-xi/</a>
                </span>
            </div>
            <div class="reprint__notice">
                <span class="reprint-meta" style="font-weight: bold;">
                    <i class="fas fa-copyright">
                        版权声明:
                    </i>
                </span>
                <span class="reprint-info">
                    本博客所有文章除特別声明外，均采用
                    <a href="https://creativecommons.org/licenses/by/4.0/deed.zh" rel="external nofollow noreferrer" target="_blank">CC BY 4.0</a>
                    许可协议。转载请注明来源
                    <a href="https://exclusiver.gitee.io" target="_blank">Doug Li</a>
                    !
                </span>
            </div>
        
    </div>

    <script async defer>
      document.addEventListener("copy", function (e) {
        let toastHTML = '<span>复制成功，请遵循本文的转载规则</span><button class="btn-flat toast-action" onclick="navToReprintStatement()" style="font-size: smaller">查看</a>';
        M.toast({html: toastHTML})
      });

      function navToReprintStatement() {
        $("html, body").animate({scrollTop: $("#reprint-statement").offset().top - 80}, 800);
      }
    </script>



            <div class="tag_share" style="display: block;">
                <div class="post-meta__tag-list" style="display: inline-block;">
                    
                        <div class="article-tag">
                            
                                <a href="/tags/MySQL/">
                                    <span class="chip bg-color">MySQL</span>
                                </a>
                            
                                <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">
                                    <span class="chip bg-color">数据库</span>
                                </a>
                            
                                <a href="/tags/%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/">
                                    <span class="chip bg-color">索引优化</span>
                                </a>
                            
                                <a href="/tags/SQL/">
                                    <span class="chip bg-color">SQL</span>
                                </a>
                            
                        </div>
                    
                </div>
                <div class="post_share" style="zoom: 80%; width: fit-content; display: inline-block; float: right; margin: -0.15rem 0;">
                    <link rel="stylesheet" type="text/css" href="/libs/share/css/share.min.css">

<div id="article-share">
    
    
    <div class="social-share" data-sites="twitter,facebook,google,qq,qzone,wechat,weibo,douban,linkedin" data-wechat-qrcode-helper="<p>微信扫一扫即可分享！</p>"></div>
    <script src="/libs/share/js/social-share.min.js"></script>
    

    

</div>

                </div>
            </div>
            
                <style>
    #reward {
        margin: 40px 0;
        text-align: center;
    }

    #reward .reward-link {
        font-size: 1.4rem;
        line-height: 38px;
    }

    #reward .btn-floating:hover {
        box-shadow: 0 6px 12px rgba(0, 0, 0, 0.2), 0 5px 15px rgba(0, 0, 0, 0.2);
    }

    #rewardModal {
        width: 320px;
        height: 350px;
    }

    #rewardModal .reward-title {
        margin: 15px auto;
        padding-bottom: 5px;
    }

    #rewardModal .modal-content {
        padding: 10px;
    }

    #rewardModal .close {
        position: absolute;
        right: 15px;
        top: 15px;
        color: rgba(0, 0, 0, 0.5);
        font-size: 1.3rem;
        line-height: 20px;
        cursor: pointer;
    }

    #rewardModal .close:hover {
        color: #ef5350;
        transform: scale(1.3);
        -moz-transform:scale(1.3);
        -webkit-transform:scale(1.3);
        -o-transform:scale(1.3);
    }

    #rewardModal .reward-tabs {
        margin: 0 auto;
        width: 210px;
    }

    .reward-tabs .tabs {
        height: 38px;
        margin: 10px auto;
        padding-left: 0;
    }

    .reward-content ul {
        padding-left: 0 !important;
    }

    .reward-tabs .tabs .tab {
        height: 38px;
        line-height: 38px;
    }

    .reward-tabs .tab a {
        color: #fff;
        background-color: #ccc;
    }

    .reward-tabs .tab a:hover {
        background-color: #ccc;
        color: #fff;
    }

    .reward-tabs .wechat-tab .active {
        color: #fff !important;
        background-color: #22AB38 !important;
    }

    .reward-tabs .alipay-tab .active {
        color: #fff !important;
        background-color: #019FE8 !important;
    }

    .reward-tabs .reward-img {
        width: 210px;
        height: 210px;
    }
</style>

<div id="reward">
    <a href="#rewardModal" class="reward-link modal-trigger btn-floating btn-medium waves-effect waves-light red">赏</a>

    <!-- Modal Structure -->
    <div id="rewardModal" class="modal">
        <div class="modal-content">
            <a class="close modal-close"><i class="fas fa-times"></i></a>
            <h4 class="reward-title">博主写的不错，请他喝加菲</h4>
            <div class="reward-content">
                <div class="reward-tabs">
                    <ul class="tabs row">
                        <li class="tab col s6 alipay-tab waves-effect waves-light"><a href="#alipay">支付宝</a></li>
                        <li class="tab col s6 wechat-tab waves-effect waves-light"><a href="#wechat">微 信</a></li>
                    </ul>
                    <div id="alipay">
                        <img src="/medias/reward/alipay.jpg" class="reward-img" alt="支付宝打赏二维码">
                    </div>
                    <div id="wechat">
                        <img src="/medias/reward/wechat.png" class="reward-img" alt="微信打赏二维码">
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
    $(function () {
        $('.tabs').tabs();
    });
</script>
            
        </div>
    </div>

    

    

    
        <div class="disqus-card card" data-aos="fade-up">
    <div id="disqus_thread" class="card-content">
        <noscript>Please enable JavaScript to view the
            <a href="https://disqus.com/?ref_noscript" target="_blank" rel="noopener">comments powered by Disqus.</a>
        </noscript>
    </div>
</div>

<script type="text/javascript">
    disqus_config = function () {
        this.page.url = 'https://exclusiver.gitee.io/2020/02/27/mysql-xiang-guan-ji-chu-yu-sql-suo-yin-you-hua-fen-xi/';
        this.page.identifier = '/2020/02/27/mysql-xiang-guan-ji-chu-yu-sql-suo-yin-you-hua-fen-xi/';
        this.page.title = 'MySQL相关基础与SQL索引优化分析';
    };
    let disqus_shortname = '';

    (function () { // DON'T EDIT BELOW THIS LINE
        let d = document, s = d.createElement('script');
        // 如：s.src = 'https://blinkfox.disqus.com/embed.js';
        s.src = '//' + disqus_shortname + '.disqus.com/embed.js';
        s.setAttribute('data-timestamp', +new Date());
        (d.head || d.body).appendChild(s);
    })();
</script>
    

    

    

    

<article id="prenext-posts" class="prev-next articles">
    <div class="row article-row">
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge left-badge text-color">
                <i class="fas fa-chevron-left"></i>&nbsp;上一篇</div>
            <div class="card">
                <a href="/2020/02/28/na-xie-nian-wo-chang-yong-de-idea-kuai-jie-jian/">
                    <div class="card-image">
                        
                        
                        <img src="/medias/featureimages/12.jpg" class="responsive-img" alt="那些年我常用的IDEA快捷键">
                        
                        <span class="card-title">那些年我常用的IDEA快捷键</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary block-with-text">
                        
                            




那些年我常用的IDEA快捷键以下是我平时使用较多的IDEA快捷键整理，仅代表个人看法。
Ctrl


快捷键
说明



Ctrl + B
进入光标所在的方法/变量的接口或是定义位置


Ctrl + C
复制光标所在行或复制选中
                        
                    </div>
                    <div class="publish-info">
                        <span class="publish-date">
                            <i class="far fa-clock fa-fw icon-date"></i>2020-02-28
                        </span>
                        <span class="publish-author">
                            
                            <i class="fas fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/Java/" class="post-category">
                                    Java
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/Java/">
                        <span class="chip bg-color">Java</span>
                    </a>
                    
                    <a href="/tags/IDEA/">
                        <span class="chip bg-color">IDEA</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
        
        <div class="article col s12 m6" data-aos="fade-up">
            <div class="article-badge right-badge text-color">
                下一篇&nbsp;<i class="fas fa-chevron-right"></i>
            </div>
            <div class="card">
                <a href="/2020/02/12/shu-ju-jie-gou-yu-suan-fa-java-pai-xu-suan-fa-zhi-mou-pao-pai-xu-ji-you-hua/">
                    <div class="card-image">
                        
                        
                        <img src="/medias/featureimages/19.jpg" class="responsive-img" alt="数据结构与算法——Java排序算法之冒泡排序及优化">
                        
                        <span class="card-title">数据结构与算法——Java排序算法之冒泡排序及优化</span>
                    </div>
                </a>
                <div class="card-content article-content">
                    <div class="summary block-with-text">
                        
                            通过增加了判断数组在某一轮下是否已经有序的标识来提高冒泡排序的运算效率




排序算法是我们在数据结构与算法中必学掌握的重点，下面着重以代码的方式复习一下冒泡排序。


普通的冒泡排序

//普通的冒泡排序：比较相邻元素之间的大小，如果发
                        
                    </div>
                    <div class="publish-info">
                            <span class="publish-date">
                                <i class="far fa-clock fa-fw icon-date"></i>2020-02-12
                            </span>
                        <span class="publish-author">
                            
                            <i class="fas fa-bookmark fa-fw icon-category"></i>
                            
                            <a href="/categories/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E4%B8%8E%E7%AE%97%E6%B3%95/" class="post-category">
                                    数据结构与算法
                                </a>
                            
                            
                        </span>
                    </div>
                </div>
                
                <div class="card-action article-tags">
                    
                    <a href="/tags/Java/">
                        <span class="chip bg-color">Java</span>
                    </a>
                    
                    <a href="/tags/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E4%B8%8E%E7%AE%97%E6%B3%95/">
                        <span class="chip bg-color">数据结构与算法</span>
                    </a>
                    
                    <a href="/tags/%E6%8E%92%E5%BA%8F/">
                        <span class="chip bg-color">排序</span>
                    </a>
                    
                    <a href="/tags/%E5%86%92%E6%B3%A1%E6%8E%92%E5%BA%8F/">
                        <span class="chip bg-color">冒泡排序</span>
                    </a>
                    
                </div>
                
            </div>
        </div>
        
    </div>
</article>

</div>



<!-- 代码块功能依赖 -->
<script type="text/javascript" src="/libs/codeBlock/codeBlockFuction.js"></script>

<!-- 代码语言 -->

<script type="text/javascript" src="/libs/codeBlock/codeLang.js"></script>

    
<!-- 代码块复制 -->

<script type="text/javascript" src="/libs/codeBlock/codeCopy.js"></script>


<!-- 代码块收缩 -->

<script type="text/javascript" src="/libs/codeBlock/codeShrink.js"></script>


<!-- 代码块折行 -->

<style type="text/css">
code[class*="language-"], pre[class*="language-"] { white-space: pre !important; }
</style>

    </div>
    <div id="toc-aside" class="expanded col l3 hide-on-med-and-down">
        <div class="toc-widget">
            <div class="toc-title"><i class="far fa-list-alt"></i>&nbsp;&nbsp;目录</div>
            <div id="toc-content"></div>
        </div>
    </div>
</div>

<!-- TOC 悬浮按钮. -->

<div id="floating-toc-btn" class="hide-on-med-and-down">
    <a class="btn-floating btn-large bg-color">
        <i class="fas fa-list-ul"></i>
    </a>
</div>


<script src="/libs/tocbot/tocbot.min.js"></script>
<script>
    $(function () {
        tocbot.init({
            tocSelector: '#toc-content',
            contentSelector: '#articleContent',
            headingsOffset: -($(window).height() * 0.4 - 45),
            collapseDepth: Number('0'),
            headingSelector: 'h2, h3, h4'
        });

        // modify the toc link href to support Chinese.
        let i = 0;
        let tocHeading = 'toc-heading-';
        $('#toc-content a').each(function () {
            $(this).attr('href', '#' + tocHeading + (++i));
        });

        // modify the heading title id to support Chinese.
        i = 0;
        $('#articleContent').children('h2, h3, h4').each(function () {
            $(this).attr('id', tocHeading + (++i));
        });

        // Set scroll toc fixed.
        let tocHeight = parseInt($(window).height() * 0.4 - 64);
        let $tocWidget = $('.toc-widget');
        $(window).scroll(function () {
            let scroll = $(window).scrollTop();
            /* add post toc fixed. */
            if (scroll > tocHeight) {
                $tocWidget.addClass('toc-fixed');
            } else {
                $tocWidget.removeClass('toc-fixed');
            }
        });

        
        /* 修复文章卡片 div 的宽度. */
        let fixPostCardWidth = function (srcId, targetId) {
            let srcDiv = $('#' + srcId);
            if (srcDiv.length === 0) {
                return;
            }

            let w = srcDiv.width();
            if (w >= 450) {
                w = w + 21;
            } else if (w >= 350 && w < 450) {
                w = w + 18;
            } else if (w >= 300 && w < 350) {
                w = w + 16;
            } else {
                w = w + 14;
            }
            $('#' + targetId).width(w);
        };

        // 切换TOC目录展开收缩的相关操作.
        const expandedClass = 'expanded';
        let $tocAside = $('#toc-aside');
        let $mainContent = $('#main-content');
        $('#floating-toc-btn .btn-floating').click(function () {
            if ($tocAside.hasClass(expandedClass)) {
                $tocAside.removeClass(expandedClass).hide();
                $mainContent.removeClass('l9');
            } else {
                $tocAside.addClass(expandedClass).show();
                $mainContent.addClass('l9');
            }
            fixPostCardWidth('artDetail', 'prenext-posts');
        });
        
    });
</script>

    

</main>



    <footer class="page-footer bg-color">
    <div class="container row center-align" style="margin-bottom: 15px !important;">
        <div class="col s12 m8 l8 copy-right">
            Copyright&nbsp;&copy;
            <span id="year">2019</span>
            <a href="https://exclusiver.gitee.io" target="_blank">Doug Li</a>
            |&nbsp;Powered by&nbsp;<a href="https://github.com/copyers" target="_blank">Zheng</a>
            <!-- |&nbsp;Github&nbsp;<a href="https://github.com/copyers" target="_blank">Matery</a> -->
            <br>
            
            &nbsp;<i class="fas fa-chart-area"></i>&nbsp;站点总字数:&nbsp;<span
                class="white-color">14.8k</span>&nbsp;字
            
            
            
            
            
            
            <span id="busuanzi_container_site_pv">
                |&nbsp;<i class="far fa-eye"></i>&nbsp;总访问量:&nbsp;<span id="busuanzi_value_site_pv"
                    class="white-color"></span>&nbsp;次
            </span>
            
            
            <span id="busuanzi_container_site_uv">
                |&nbsp;<i class="fas fa-users"></i>&nbsp;总访问人数:&nbsp;<span id="busuanzi_value_site_uv"
                    class="white-color"></span>&nbsp;人
            </span>
            
            <br>
            
            <span id="sitetime">载入运行时间...</span>
            <script>
                function siteTime() {
                    var seconds = 1000;
                    var minutes = seconds * 60;
                    var hours = minutes * 60;
                    var days = hours * 24;
                    var years = days * 365;
                    var today = new Date();
                    var startYear = "2019";
                    var startMonth = "6";
                    var startDate = "28";
                    var startHour = "0";
                    var startMinute = "0";
                    var startSecond = "0";
                    var todayYear = today.getFullYear();
                    var todayMonth = today.getMonth() + 1;
                    var todayDate = today.getDate();
                    var todayHour = today.getHours();
                    var todayMinute = today.getMinutes();
                    var todaySecond = today.getSeconds();
                    var t1 = Date.UTC(startYear, startMonth, startDate, startHour, startMinute, startSecond);
                    var t2 = Date.UTC(todayYear, todayMonth, todayDate, todayHour, todayMinute, todaySecond);
                    var diff = t2 - t1;
                    var diffYears = Math.floor(diff / years);
                    var diffDays = Math.floor((diff / days) - diffYears * 365);
                    var diffHours = Math.floor((diff - (diffYears * 365 + diffDays) * days) / hours);
                    var diffMinutes = Math.floor((diff - (diffYears * 365 + diffDays) * days - diffHours * hours) /
                        minutes);
                    var diffSeconds = Math.floor((diff - (diffYears * 365 + diffDays) * days - diffHours * hours -
                        diffMinutes * minutes) / seconds);
                    if (startYear == todayYear) {
                        document.getElementById("year").innerHTML = todayYear;
                        document.getElementById("sitetime").innerHTML = "本站已安全运行 " + diffDays + " 天 " + diffHours +
                            " 小时 " + diffMinutes + " 分钟 " + diffSeconds + " 秒";
                    } else {
                        document.getElementById("year").innerHTML = startYear + " - " + todayYear;
                        document.getElementById("sitetime").innerHTML = "本站已安全运行 " + diffYears + " 年 " + diffDays +
                            " 天 " + diffHours + " 小时 " + diffMinutes + " 分钟 " + diffSeconds + " 秒";
                    }
                }
                setInterval(siteTime, 1000);
            </script>
            
            <br>
            
            <span id="icp"><img src="/medias/icp.png" style="vertical-align: text-bottom;" />
                <a href="" target="_blank"></a>
            </span>
            
        </div>
        <div class="col s12 m4 l4 social-link social-statis">
    <a href="https://github.com/copyers" class="tooltipped" target="_blank" data-tooltip="访问我的GitHub" data-position="top" data-delay="50">
        <i class="fab fa-github"></i>
    </a>



    <a href="mailto:1206227301@qq.com" class="tooltipped" target="_blank" data-tooltip="邮件联系我" data-position="top" data-delay="50">
        <i class="fas fa-envelope-open"></i>
    </a>







    <a href="tencent://AddContact/?fromId=50&fromSubId=1&subcmd=all&uin=1206227301" class="tooltipped" target="_blank" data-tooltip="QQ联系我: 1206227301" data-position="top" data-delay="50">
        <i class="fab fa-qq"></i>
    </a>





    <a href="https://www.zhihu.com/people/zheng-quan-74" class="tooltipped" target="_blank" data-tooltip="关注我的知乎: https://www.zhihu.com/people/zheng-quan-74" data-position="top" data-delay="50">
        <i class="fab fa-zhihu1">知</i>
    </a>



    <a href="/atom.xml" class="tooltipped" target="_blank" data-tooltip="RSS 订阅" data-position="top" data-delay="50">
        <i class="fas fa-rss"></i>
    </a>

</div>
    </div>
</footer>

<div class="progress-bar"></div>


    <!-- 搜索遮罩框 -->
<div id="searchModal" class="modal">
    <div class="modal-content">
        <div class="search-header">
            <span class="title"><i class="fas fa-search"></i>&nbsp;&nbsp;搜索</span>
            <input type="search" id="searchInput" name="s" placeholder="请输入搜索的关键字"
                   class="search-input">
        </div>
        <div id="searchResult"></div>
    </div>
</div>

<script src="/js/search.js"></script>
<script type="text/javascript">
$(function () {
    searchFunc("/" + "search.xml", 'searchInput', 'searchResult');
});
</script>
    <!-- 回到顶部按钮 -->
<div id="backTop" class="top-scroll">
    <a class="btn-floating btn-large waves-effect waves-light" href="#!">
        <i class="fas fa-arrow-up"></i>
    </a>
</div>


    <script src="/libs/materialize/materialize.min.js"></script>
    <script src="/libs/masonry/masonry.pkgd.min.js"></script>
    <script src="/libs/aos/aos.js"></script>
    <script src="/libs/scrollprogress/scrollProgress.min.js"></script>
    <script src="/libs/lightGallery/js/lightgallery-all.min.js"></script>
    <script src="/js/matery.js"></script>

    <!-- Global site tag (gtag.js) - Google Analytics -->


    <!-- Baidu Analytics -->

    <!-- Baidu Push -->

<script>
    (function () {
        var bp = document.createElement('script');
        var curProtocol = window.location.protocol.split(':')[0];
        if (curProtocol === 'https') {
            bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
        } else {
            bp.src = 'http://push.zhanzhang.baidu.com/push.js';
        }
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(bp, s);
    })();
</script>

    
    <script src="/libs/others/clicklove.js" async="async"></script>
    
    
    <script async src="/libs/others/busuanzi.pure.mini.js"></script>
    

    

    

    
    <script type="text/javascript" color="0,0,255"
        pointColor="0,0,255" opacity='0.7'
        zIndex="-1" count="99"
        src="/libs/background/canvas-nest.js"></script>
    

    
    
    <script type="text/javascript" size="150" alpha='0.6'
        zIndex="-1" src="/libs/background/ribbon-refresh.min.js" async="async"></script>
    

    
    <script type="text/javascript" src="/libs/background/ribbon-dynamic.js" async="async"></script>
    
    
    
    <script src="/libs/instantpage/instantpage.js" type="module"></script>
    

</body>

</html>
